package cn.edu.scau.cmi.tangqingning.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.edu.scau.cmi.tangqingning.dao.StudentDao;
import cn.edu.scau.cmi.tangqingning.model.Group;
import cn.edu.scau.cmi.tangqingning.model.Student;
import cn.edu.scau.cmi.tangqingning.tools.DbConnection;
import cn.edu.scau.cmi.tangqingning.tools.StringUtil;

/**
 * 数据访问层-学生的信息操作<br>
 * 与数据库的交互操作所需要用到的方法<br>
 * 编写代码的时候要注意数据库的信息要与编号要一一对应
 * 
 * @author 谭祖杰
 *
 */
public class StudentDaoImpl implements StudentDao {
	private String create = "";

	/**
	 * 学生登录的判断
	 * 通过传入的学生信息 和stuperinfo表里的学生学号和密码对比，从而确定是否成功登录
	 * @param  student Student
	 * @return flag 判断数据库中是否有学生
	 */
	@Override
	public boolean isStuLogin(Student student) {
		create = "create table " + "stuperinfo" + "(STID varchar(12) NOT NULL COMMENT '学号',"
				+ "STNAME varchar(20) NOT NULL COMMENT ' 姓名'," + " STSEX varchar(10) NOT NULL COMMENT '性别',"
				+ "STAGE varchar(10) NOT NULL COMMENT '年龄'," + "STTEL varchar(50) NOT NULL COMMENT '电话',"
				+ "STDEPT varchar(50) NOT NULL COMMENT '系别'," + "STADDRESS varchar(50) NOT NULL COMMENT '地址',"
				+ "STGROUP varchar(12) NOT NULL DEFAULT '0' COMMENT '小组',"
				+ "PASSWORD varchar(12) NOT NULL COMMENT '密码',"
				+ "MARK` varchar(50) DEFAULT '00000000000000000000000000000000000000000000000000',"
				+ "primary key(STID))" + " ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		boolean flag = false;
		String sql = "select * from stuperinfo where stId=? and password=?";
		try {
			PreparedStatement pmst = conn.prepareStatement(sql);
			pmst.setLong(1, student.getStId());// 对传过来的用户名和密码进行封装
			pmst.setLong(2, student.getPassword());
			// 第一个参数是该参数在语句中的索引。参数标记具有从 1 开始的编号。 通常指的是第一个“？”
			// 第二个参数是要对第一个参数设置的值
			ResultSet rs = pmst.executeQuery();// 把数据库响应的查询结果存放在ResultSet类对象中供我们使用
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 更新学生的密码,没有则返回a为0
	 * 
	 * @param student Student
	 * @return 成功更新密码则返回1; 否则返回0
	 * @see
	 * cn.edu.scau.cmi.tangqingning.model.Student
	 */
	@Override
	public int updateStuPassWord(Student student) {
		Connection conn = DbConnection.getConnection(create, false);
		int a = 0;
		String sql = "update stuperinfo set password=? where stId=?";
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, student.getPassword());
			pstmt.setLong(2, student.getStId());
			a = pstmt.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return a;
	}

	/**
	 * 获取个人信息
	 * 通过学生的id查找学生,后面返回一个小组对象,如果没找到小组对象为0
	 * 
	 * @param stId 传入一个String类型的学生id
	 * @return group  返回一个Group类型的小组对象
	 */
	@Override
	public Group findStudentById(String stId) {
		create = "create table " + "stuperinfo" + "(STID varchar(12) NOT NULL COMMENT '学号',"
				+ "STNAME varchar(20) NOT NULL COMMENT ' 姓名'," + " STSEX varchar(10) NOT NULL COMMENT '性别',"
				+ "STAGE varchar(10) NOT NULL COMMENT '年龄'," + "STTEL varchar(50) NOT NULL COMMENT '电话',"
				+ "STDEPT varchar(50) NOT NULL COMMENT '系别'," + "STADDRESS varchar(50) NOT NULL COMMENT '地址',"
				+ "STGROUP varchar(12) NOT NULL DEFAULT '0' COMMENT '小组',"
				+ "PASSWORD varchar(12) NOT NULL COMMENT '密码',"
				+ "MARK` varchar(50) DEFAULT '00000000000000000000000000000000000000000000000000',"
				+ "primary key(STID))" + " ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		Student student = null;
		Group group = null;
		String sql = "select * from stuperinfo where stId=?";

		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, stId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				student = new Student();
				student.setStId(Long.parseLong(rs.getString("stId")));// 数据库名称不分大小写
				student.setStName(rs.getString("stName"));
				student.setStSex(rs.getString("stSex"));
				student.setStAge(rs.getString("stAge"));
				student.setStTel(Long.parseLong(rs.getString("stTel")));
				student.setStDept(rs.getString("stDept"));
				student.setStAddress(rs.getString("stAddress"));
				group = new Group(student);
				group.setGroupNumber(Integer.parseInt(rs.getString("stGroup")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return group;
	}

	/**
	 * 计算平均分
	 * 首先把小组原来的分数从数据库读取出来。然后更新数据后存进数据库里面！
	 * 计算功能平均分,界面平均分，答辩平均分，创新平均分，以及总平均分
	 * 
	 * @param group Group 
	 * @param student Student	
	 * @return true	如果成功读取出原来的数据并且更新新的数据进数据库，则为true; false 如果读取数据或者更新数据时发生错误，则为false
	 * @author 刘岳鑫
	 * @throws SQLException  如果一个错误发生，则抛出此异常
	 * @see cn.edu.scau.cmi.tangqingning.model.Group
	 * @see cn.edu.scau.cmi.tangqingning.model.Student
	 */
	@Override
	public int[] calculateScore(Group group, Student student) throws SQLException {
		Connection conn = DbConnection.getConnection(create, false);
		/* 把小组原来的分数从数据库读取出来 */
		int[] flagArray = new int[2];
		flagArray[0] = 0; // 初始标记 f0标记是否评价过该小组。0否，1是
		flagArray[1] = 0; // 初始标记 f1标记是否评价成功。0否，1是
		/* 此处是取得该学生关于评价小组的mark标志 */
		String sql = "select * from stuperinfo where stId=?";
		PreparedStatement psmt = conn.prepareStatement(sql);
		psmt.setLong(1, student.getStId());
		ResultSet rs = psmt.executeQuery();
		while (rs.next()) {
			student.setMark(rs.getString("mark"));
		}
		// 判断mark里面的某一位的状态是否为1，为1则return false；否则计算分数并更新分数。写入数据库
		char mark = student.getMark().charAt(student.getMark().length() - group.getGroupNumber());
		if (mark == '1') {
			flagArray[0] = 1;
			return flagArray;
		} else {
			try {
				String sql1 = "select * from projectdata where groupNumber=?";
				PreparedStatement psmt1 = conn.prepareStatement(sql1);
				psmt1 = conn.prepareStatement(sql1);
				psmt1.setInt(1, group.getGroupNumber());
				rs = psmt1.executeQuery();
				while (rs.next()) {
					/* 此处是取得 原来的各项平均分 */
					group.setNumberOfEvaluater(rs.getInt("numberOfEvaluater"));
					group.setFunctionScore(rs.getDouble("functionScore"));
					group.setInterfaceScore(rs.getDouble("interfaceScore"));
					group.setInnovationScore(rs.getDouble("innovationScore"));
					group.setOralDefenseScore(rs.getDouble("interfaceScore"));
					group.setAverageScore(rs.getDouble("averageScore"), 0, 0, 0);
					/* 计算分数 先求出原来各项总评分 平均分*人数 接着 新平均分=（总分+当前被评分）/（人数+1） */
					// 获得新的功能总分
					group.setFunctionTotalScore(group.getFunctionScore() * group.getNumberOfEvaluater());
					// 获得新的界面总分
					group.setInterfaceTotalScore(group.getInterfaceTotalScore() * group.getNumberOfEvaluater());
					// 获得新的创新总分
					group.setInnovationTotalScore(group.getInnovationTotalScore() * group.getNumberOfEvaluater());
					// 获得新的答辩总分
					group.setOralDefenseTotalScore(group.getOralDefenseTotalScore() * group.getNumberOfEvaluater());
					// 被评人数加1
					group.setNumberOfEvaluater(group.getNumberOfEvaluater() + 1);
					// 更新小组的分数
					group.setFunctionScore((group.getFunctionTotalScore() + student.getFunctionScoreOfEvaluateGroup())
							/ group.getNumberOfEvaluater());
					group.setInterfaceScore(
							(group.getInterfaceTotalScore() + student.getInterfaceScoreOfEvaluateGroup())
									/ group.getNumberOfEvaluater());
					group.setInnovationScore(
							(group.getInnovationTotalScore() + student.getInnovationScoreOfEvaluateGroup())
									/ group.getNumberOfEvaluater());
					group.setOralDefenseScore(
							(group.getOralDefenseTotalScore() + student.getOralDefenseScoreOfEvaluateGroup())
									/ group.getNumberOfEvaluater());
					group.setAverageScore(group.getFunctionScore(), group.getInterfaceScore(),
							group.getInnovationScore(), group.getOralDefenseScore()); // 当前的各个平均分之和
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}

			/* 存进数据库里面 */
			String sql2 = "update projectdata set numberOfEvaluater=?, functionScore=?, "
					+ "interfaceScore=?, innovationScore=?, oralDefenseScore=?, averageScore=? where groupNumber=? ";
			try {
				PreparedStatement psmt2 = conn.prepareStatement(sql2);
				psmt2 = conn.prepareStatement(sql2);
				psmt2.setInt(1, group.getNumberOfEvaluater());
				psmt2.setDouble(2, StringUtil.toDecimals(group.getFunctionScore()));
				psmt2.setDouble(3, StringUtil.toDecimals(group.getInterfaceScore()));
				psmt2.setDouble(4, StringUtil.toDecimals(group.getInnovationScore()));
				psmt2.setDouble(5, StringUtil.toDecimals(group.getOralDefenseScore()));
				psmt2.setDouble(6, StringUtil.toDecimals(group.getAverageScore()));
				psmt2.setInt(7, group.getGroupNumber());
				psmt2.executeUpdate(); //
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// 修改mark标志
			StringBuilder strBuilder = new StringBuilder(student.getMark());
			strBuilder.setCharAt(student.getMark().length() - group.getGroupNumber(), '1');
			student.setMark(strBuilder.toString());
			// 更新mark标志

			String sql3 = "update stuperinfo set mark=? where stId=? ";
			PreparedStatement psmt3 = conn.prepareStatement(sql3);
			psmt3 = conn.prepareStatement(sql3);
			psmt3.setString(1, student.getMark());
			psmt3.setLong(2, student.getStId());
			int i = psmt3.executeUpdate();
			if (i == 1) {
				flagArray[1] = 1;
				return flagArray;
			}

		}

		return flagArray;

	}

	/**
	 * 查看学生个人项目内容
	 * 通过学生学号对stuperinfo数据库表查找,如果
	 * 找到了小组编号，把小组附上小组号，对projectdata表查找该小组编号的
	 * 项目内容，项目分数,最后返回一个小组对象
	 * 
	 * @param stId	 	传入一个String类型的学生id
	 * @return group	返回一个Group类型的小组对象
	 */
	public Group findProjectContentById(String stId) {
		create = "create table " + "stuperinfo" + "(STID varchar(12) NOT NULL COMMENT '学号',"
				+ "STNAME varchar(20) NOT NULL COMMENT ' 姓名'," + " STSEX varchar(10) NOT NULL COMMENT '性别',"
				+ "STAGE varchar(10) NOT NULL COMMENT '年龄'," + "STTEL varchar(50) NOT NULL COMMENT '电话',"
				+ "STDEPT varchar(50) NOT NULL COMMENT '系别'," + "STADDRESS varchar(50) NOT NULL COMMENT '地址',"
				+ "STGROUP varchar(12) NOT NULL DEFAULT '0' COMMENT '小组',"
				+ "PASSWORD varchar(12) NOT NULL COMMENT '密码',"
				+ "MARK` varchar(50) DEFAULT '00000000000000000000000000000000000000000000000000',"
				+ "primary key(STID))" + " ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		Student student = null;
		Group group = null;
		// 查找小组编号
		String sql = "select * from stuperinfo where stId=?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, stId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				// 获得小组编号
				group = new Group(student);
				group.setGroupNumber(Integer.parseInt(rs.getString("stGroup")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// 查找该小组编号的项目内容
		sql = "select * from projectdata where groupNumber=?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, String.valueOf(group.getGroupNumber()));
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				// 获得小组的项目信息
				group.setProIntroduction(rs.getString("proIntroduction"));
				group.setProRequirement(rs.getString("proRequirement"));
				group.setProDivision(rs.getString("proDivision"));
				// 获得小组的被评分数
				group.setNumberOfEvaluater(rs.getInt("numberOfEvaluater"));
				group.setFunctionScore(rs.getDouble("functionScore"));
				group.setInterfaceScore(rs.getDouble("interfaceScore"));
				group.setInnovationScore(rs.getDouble("innovationScore"));
				group.setOralDefenseScore(rs.getDouble("interfaceScore"));
				group.setAverageScore(rs.getDouble("averageScore"), 0, 0, 0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return group;
	}

	/**
	 * 查看全体小组信息
	 * 对projectdata数据库表查找,如果查找到数据则存在list里面
	 * 
	 * @return List	返回一个Group类型的小组对象数组List
	 */
	@SuppressWarnings("unchecked")
	public List searchOtherGroup() {
		create = "create table " + "projectdata" + "(GROUPNUMBER int(12) NOT NULL AUTO_INCREMENT COMMENT '小组编号' ,"
				+ " PROINTRODUCTION varchar(1000) DEFAULT '0' COMMENT '项目介绍',"
				+ " PROREQUIREMENT varchar(1000) DEFAULT '0' COMMENT '项目要求',"
				+ " PRODIVISION varchar(1000) DEFAULT '0' COMMENT '项目分工',"
				+ " NUMBEROFEVALUATER varchar(11) DEFAULT '0' COMMENT '被评人数',"
				+ "FUNCTIONSCORE varchar(5) DEFAULT '0' COMMENT '功能平均分',"
				+ "INTERFACESCORE varchar(5) DEFAULT '0' COMMENT '界面效果平均分',"
				+ "INNOVATIONSCORE varchar(5) DEFAULT '0' COMMENT '创新性平均分',"
				+ "ORALDEFENSESCORE varchar(5) DEFAULT '0' COMMENT '答辩表现平均分',"
				+ "AVERAGESCORE varchar(5) DEFAULT '0' COMMENT '总分'," + "primary key(GROUPNUMBER))"
				+ " ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		List list = new ArrayList<Group>();
		String sql = "select * from projectdata";
		try {
			Statement smt = conn.createStatement();
			ResultSet rs = smt.executeQuery(sql);
			while (rs.next()) {
				int groupNumber = rs.getInt("groupNumber");
				String proIntroduction = rs.getString("proIntroduction");
				String proRequirement = rs.getString("proRequirement");
				String proDivision = rs.getString("proDivision");
				int numberOfEvaluater = rs.getInt("numberOfEvaluater");
				double functionScore = rs.getDouble("functionScore");
				double interfaceScore = rs.getDouble("interfaceScore");
				double innovationScore = rs.getDouble("innovationScore");
				double oralDefenseScore = rs.getDouble("oralDefenseScore");
				double averageScore = rs.getDouble("averageScore");
				Group group = new Group(groupNumber, proIntroduction, proRequirement, proDivision, numberOfEvaluater,
						functionScore, interfaceScore, innovationScore, oralDefenseScore, averageScore);
				list.add(group);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查看小组的项目信息
	 * 对projectdata数据库表查找项目信息,如果查找到数据则存在group对象里面
	 * @param groupNumber 传入小组编号
	 * @return List	返回一个Group类型的小组对象数组List
	 */
	@Override
	public Group findProjectContentByGroupNumber(int groupNumber) {
		create = "create table " + "projectdata" + "(GROUPNUMBER int(12) NOT NULL AUTO_INCREMENT COMMENT '小组编号' ,"
				+ " PROINTRODUCTION varchar(1000) DEFAULT '0' COMMENT '项目介绍',"
				+ " PROREQUIREMENT varchar(1000) DEFAULT '0' COMMENT '项目要求',"
				+ " PRODIVISION varchar(1000) DEFAULT '0' COMMENT '项目分工',"
				+ " NUMBEROFEVALUATER varchar(11) DEFAULT '0' COMMENT '被评人数',"
				+ "FUNCTIONSCORE varchar(5) DEFAULT '0' COMMENT '功能平均分',"
				+ "INTERFACESCORE varchar(5) DEFAULT '0' COMMENT '界面效果平均分',"
				+ "INNOVATIONSCORE varchar(5) DEFAULT '0' COMMENT '创新性平均分',"
				+ "ORALDEFENSESCORE varchar(5) DEFAULT '0' COMMENT '答辩表现平均分',"
				+ "AVERAGESCORE varchar(5) DEFAULT '0' COMMENT '总分'," + "primary key(GROUPNUMBER))"
				+ " ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8";
		Connection conn = DbConnection.getConnection(create, true);
		Group group = null;
		// 查找该小组编号的项目内容
		String sql = "select * from projectdata where groupNumber=?";
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, groupNumber);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				// 获得小组的项目信息
				group = new Group();
				group.setGroupNumber(Integer.parseInt(rs.getString("groupNumber")));
				group.setProIntroduction(rs.getString("proIntroduction"));
				group.setProRequirement(rs.getString("proRequirement"));
				group.setProDivision(rs.getString("proDivision"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return group;
	}

}
